package jsyf.oa.dao.crm;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import freemarker.template.utility.StringUtil;
import jsyf.oa.entity.crm.ExhibitionCustomerInfoEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * Created by WHH-PC on 2017/12/26.
 */
@Repository
public class ExhibitionCustomerInfoDao extends BaseDao {
    private static final long serialVersionUID = 1L;

    public ExhibitionCustomerInfoDao() {
        super.iBaseEntity = new ExhibitionCustomerInfoEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, ExhibitionCustomerInfoEntity.class);
    }

    public List<JSONObject> getExihibitionCustomerInfo(Integer pageIndex, String userId, String exhibitionId, String key, Boolean isSupport, Boolean isExport, Boolean isAllRegion) {
        Integer pageSize = 30;
        if (isExport)
            pageSize = 10000;
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT H.regionName bigRegionName,H.userName bigUserName,D.userName,");
        sbSql.append("D.regionName,D.regionId,E.CUSTOM_NUM,E.DEPOSIT_NUM,B.NAME,B.TEL,A.* ");
        sbSql.append("FROM T_CRM_EXHIBITION_CUSTOMER_INFO A ");
        sbSql.append("LEFT JOIN T_WRT_CUSTOMER B ON A.CUSTOMER_ID=B.ID ");
        sbSql.append("LEFT JOIN V_REGION_MANAGER D ON A.CREATE_USER_ID=D.userId ");
        sbSql.append("LEFT JOIN T_CRM_EXHIBITION_AIMS E ON D.regionId=E.REGION_ID  AND E.EXHIBITION_ID='" + exhibitionId + "' ");
        sbSql.append("LEFT JOIN T_SYS_USER G ON A.CREATE_USER_ID=G.ID ");
        sbSql.append("LEFT JOIN V_REGION_MANAGER H ON G.PARENT_ID=H.userId ");
        if (!isAllRegion) {
            if (isSupport) {
                sbSql.append("LEFT JOIN T_SYS_USER_2_REGION I ON I.REGION_ID=H.regionId ");
                sbSql.append("WHERE I.USER_ID='" + userId + "' ");
            } else
                sbSql.append("WHERE A.CREATE_USER_ID='" + userId + "' ");
        } else
            sbSql.append("WHERE 1=1 ");
        if (!StringUtils.isEmpty(key))
            sbSql.append("AND (B.NAME='" + key + "' OR B.TEL='" + key + "') ");
        sbSql.append("AND A.EXHIBITION_ID='" + exhibitionId + "' ");
        sbSql.append("order by A.CREATE_USER_ID,TRACK_STATUS ");
        sbSql.append("LIMIT ");
        sbSql.append((pageIndex - 1) * pageSize);
        sbSql.append("," + pageSize);
        return queryForJsonList(sbSql.toString());
    }

    public Integer getExihibitionCustomerInfoCount(String userId, String exhibitionId, String key, Boolean isSupport, Boolean isAllRegion) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select count(1) c from T_CRM_EXHIBITION_CUSTOMER_INFO A ");
        sbSql.append("LEFT JOIN T_WRT_CUSTOMER B ON A.CUSTOMER_ID=B.ID ");
        sbSql.append("LEFT JOIN T_SYS_USER G ON A.CREATE_USER_ID=G.ID ");
        sbSql.append("LEFT JOIN V_REGION_MANAGER H ON G.PARENT_ID=H.userId ");
        if (!isAllRegion) {
            if (isSupport) {
                sbSql.append("LEFT JOIN T_SYS_USER_2_REGION I ON I.REGION_ID=H.regionId ");
                sbSql.append("WHERE I.USER_ID='" + userId + "' ");
            } else
                sbSql.append("WHERE A.CREATE_USER_ID='" + userId + "' ");
        } else
            sbSql.append("WHERE 1=1 ");
        if (!StringUtils.isEmpty(key))
            sbSql.append("AND (B.NAME='" + key + "' OR B.TEL='" + key + "') ");
        sbSql.append("AND A.EXHIBITION_ID='" + exhibitionId + "' ");

        return Integer.parseInt(queryForJsonList(sbSql.toString()).get(0).get("c").toString());
    }


    public List<JSONObject> getExhibitionIntentionList(String exhibitionId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select C.NAME modelName,A.* from T_CRM_EXHIBITION_INTENTION A ");
        sbSql.append("left join T_CRM_EXHIBITION_CUSTOMER_INFO B on A.EXHIBITION_INFO_ID=B.ID ");
        sbSql.append("left join T_DIC_EQUIPMENT_MODEL C ON A.EQIPMENT_MODEL_ID=C.ID ");
        sbSql.append(" WHERE EQIPMENT_MODEL_ID IS NOT NULL AND B.EXHIBITION_ID='" + exhibitionId + "' ");

        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getTrackRecordList(String cusId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT VISIT_CONTENT,CREATE_TIME ");
        sb.append("FROM T_WRT_CUSTOMER_VISIT WHERE CUSTOMER_ID='" + cusId + "' ");
        sb.append("AND  VISIT_CONTENT IS NOT NULL  ORDER BY CREATE_TIME DESC ");
        return queryForJsonList(sb.toString());
    }

    public Map<String, JSONObject> getExhibitionIntentionMap(String exhibitionId) {
        Map<String, JSONObject> map = new HashMap<>();

        getExhibitionIntentionList(exhibitionId).forEach(jsonObject -> {
            String exhibitionInfoId = jsonObject.get("EXHIBITION_INFO_ID").toString();
            String modelName = jsonObject.get("modelName").toString();
            String quote = jsonObject.get("QUOTE").toString();
            String acceptPrice = jsonObject.get("ACCEPT_PRICE").toString();

            if (StringUtils.isEmpty(modelName)) {
                return;
            }
            if (map.containsKey(exhibitionInfoId)) {
                JSONObject obj = map.get(exhibitionInfoId);
                String a = obj.get("modelName").toString() + "/" + modelName;
                String b = obj.get("QUOTE").toString() + "/" + quote;
                String c = obj.get("ACCEPT_PRICE").toString() + "/" + acceptPrice;

                obj.put("modelName", a);
                obj.put("QUOTE", b);
                obj.put("ACCEPT_PRICE", c);
            } else
                map.put(exhibitionInfoId, jsonObject);
        });
        return map;
    }

    /**
     * 根据销售支持获取销售人员
     *
     * @param userId
     * @return
     */
    public List<JSONObject> getSalerBySalerSupport(String userId, String userName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select B.userId,B.userName,B.tel,B.regionId,A.* from T_SYS_USER_2_REGION A ");
        sb.append("LEFT JOIN V_REGION_MANAGER B ON A.REGION_ID=B.regionId ");
        sb.append("WHERE A.USER_ID='" + userId + "'  ");
        if (StringUtils.isNotEmpty(userName)) {
            sb.append(" and B.userName like '%" + userName + "%' ");
        }
        sb.append("and userId is not null ");
        return queryForJsonList(sb.toString());
    }

    public List<JSONObject> getSupport2RegionList() {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from V_REGION_SUPPORT ");
        return queryForJsonList(sb.toString());
    }

    public Map<String, JSONObject> getRegion2Support() {
        Map<String, JSONObject> map = new HashMap<>();
        getSupport2RegionList().forEach(jsonObject -> {
            String regionId = jsonObject.get("regionId").toString();
            map.put(regionId, jsonObject);
        });
        return map;
    }

    public List<JSONObject> getTrackList(String exhibitionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("select a.CUSTOMER_ID,a.CREATE_TIME TRACK_TIME,a.VISIT_CONTENT,a.FUND_CONDITION  ");
        sb.append("from T_WRT_CUSTOMER_VISIT a,( ");
        sb.append("     select CUSTOMER_ID,max(create_time) create_time from T_WRT_CUSTOMER_VISIT group by CUSTOMER_ID ");
        sb.append(") b where a.CUSTOMER_ID = b.CUSTOMER_ID and a.create_time = b.create_time and a.VISIT_CONTENT is not null  ");
        sb.append("and a.customer_id in (");
        sb.append("   select CUSTOMER_ID from T_CRM_EXHIBITION_CUSTOMER_INFO where EXHIBITION_ID='" + exhibitionId + "' ");
        sb.append(")  order by a.CUSTOMER_ID ");
        return queryForJsonList(sb.toString());
    }

    public Map<String, JSONObject> getTrackMap(String exhibitionId) {
        Map<String, JSONObject> map = new HashMap<>();
        getTrackList(exhibitionId).forEach(jsonObject -> {
            String customerId = jsonObject.get("CUSTOMER_ID").toString();
            map.put(customerId, jsonObject);
        });
        return map;
    }

}
